with result_table as (
    select case
               when (before_send_station_network_type in (3, 4) or before_send_station_network_type is null) and
                    if(send_station_next_statation_type is not null and send_station_next_statation_type <> '',
                       send_station_next_statation_type, send_station_send_next_network_type) = 2 then 1
               when before_send_station_network_type in (3, 4) and
                    if(send_station_next_statation_type is not null and send_station_next_statation_type <> '',
                       send_station_next_statation_type, send_station_send_next_network_type) in (3, 4)
                   then 2
               when before_send_station_network_type = 2 and
                    if(send_station_next_statation_type is not null and send_station_next_statation_type <> '',
                       send_station_next_statation_type, send_station_send_next_network_type) = 2 then 3
               when (before_send_station_network_type = 2 or before_send_station_network_type is null) and
                    if(send_station_next_statation_type is not null and send_station_next_statation_type <> '',
                       send_station_next_statation_type, send_station_send_next_network_type) in (3, 4) then 4
               else 0 end as type              --4进港3中转2同城1出港
         , waybill_no                          --运单号
         , send_station_network_code           --发件站点code
         , send_station_network_name           --发件站点name
         , send_station_network_type           --发件站点类型
         , send_station_number                 --发件站点序列号
         , sent_station_send_packagecode       --发件站点发件扫描包号
         , sent_station_arrival_packagecode    --发件站点到件扫描包号
         , send_station_next_station           --发件下一站
         , next_station_network_name           --路段目的站点名称
         , send_station_next_statation_type    --发件下一站站点类型
         , source_arrival                      --A站点到件来源
         , send_station_arrival_early_time     --路段始发站点最在到件时间
         , send_station_send_later_time        --路段始发站点最晚发件时间
         , send_station_bagging_early_scantime --发件站点最早建包时间
         , send_station_bagging_packagecode    --路段始发建包包号
         , before_send_station_network_code    --始发上一站code
         , before_send_station_network_name    --始发上一站name
         , before_send_station_network_type    --始发上一站站点类型
         , send_station_send_next_network_code --发件规划下一站
         , send_station_send_next_network_type --发件规划下一站类型
         , send_station_unbagging_packagecode
    from (
             select waybill_no                                                                                           --运单号
                  , send_station_network_code                                                                            --发件站点code
                  , send_station_network_name                                                                            --发件站点name
                  , send_station_network_type                                                                            --发件站点类型
                  , send_station_number                                                                                  --发件站点序列号
                  , sent_station_send_packagecode                                                                        --发件站点发件扫描包号
                  , sent_station_arrival_packagecode                                                                     --发件站点到件扫描包号
                  , send_station_next_station                                                                            --发件下一站
                  , next_station_network_name                                                                            --路段目的站点名称
                  , send_station_next_statation_type                                                                     --发件下一站站点类型
                  , next_station_send_packagecode                                                                        --路段目的站点发件扫描包号
                  , next_station_arrival_packagecode                                                                     --路段目的站点到件扫描包号
                  , send_station_bagging_packagecode                                                                     --路段始发建包包号
                  , source_send                                                                                          --A站点发件来源
                  , source_arrival                                                                                       --A站点到件来源
                  , send_station_arrival_early_time                                                                      --路段始发站点最在到件时间
                  , send_station_send_later_time                                                                         --路段始发站点最晚发件时间
                  , send_station_bagging_early_scantime                                                                  --发件站点最早建包时间
                  , lag(send_station_network_code)
                        over (partition by waybill_no order by send_station_number ) as before_send_station_network_code --始发上一站code
                  , lag(send_station_network_name)
                        over (partition by waybill_no order by send_station_number ) as before_send_station_network_name --始发上一站name
                  , lag(send_station_network_type)
                        over (partition by waybill_no order by send_station_number ) as before_send_station_network_type --始发上一站站点类型
                  , send_station_send_next_network_code                                                                  --发件规划下一站
                  , send_station_send_next_network_type                                                                  --发件规划下一站类型
                  , send_station_unbagging_packagecode                                                                   --路段始发站点拆包包号
             from jms_dwd.dwd_wide_unsign_trace_waybill_step_dt
             where dt <= '{{ execution_date | cst_ds }}'
               and dt >= date_add('{{ execution_date | cst_ds }}', -44)
         ) t
    where to_date(send_station_arrival_early_time) = date_add('{{ execution_date | cst_ds }}', -1)
      and send_station_network_type = 2 --网点类型,1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
)
insert
overwrite
table
bidefault.dm_dev_center_operate_waybill_detail_dt
partition
(
dt
)
select t1.type                                                                                       as type                   --类型1出港2同城3中转4进港
     , t1.waybill_no                                                                                 as waybill_no             --运单号
     , t1.send_station_network_code                                                                  as network_code           --到件站点code
     , t1.send_station_network_name                                                                  as network_name           --到件站点name
     , t1.send_station_network_type                                                                  as network_type           --到件站点类型
     , t1.send_station_number                                                                        as number                 --到件站点序列号
     , t1.sent_station_send_packagecode                                                              as send_packagecode       --到件站点发件扫描包号
     , t1.sent_station_arrival_packagecode                                                           as arrival_packagecode    --到件站点到件扫描包号
     , t1.send_station_next_station                                                                  as next_network_code      --到件站点下一站code
     , t1.next_station_network_name                                                                  as next_network_name      --到件站点下一站name
     , t1.send_station_next_statation_type                                                           as next_network_type      --到件站点下一站类型
     , t1.source_arrival                                                                             as source_arrival         --到件站点到件来源
     , t1.send_station_arrival_early_time                                                            as arrival_time           --到件站点到件时间
     , t1.send_station_send_later_time                                                               as send_time              --到件站点发件时间
     , t1.send_station_bagging_early_scantime                                                        as send_build_bag_time    --到件站点建包时间
     , t1.send_station_bagging_packagecode                                                           as send_build_bag         --到件站点建包包号
     , t1.before_send_station_network_code                                                           as before_network_code    --到件站点上一站code
     , t1.before_send_station_network_name                                                           as before_network_name    --到件站点上一站name
     , t1.before_send_station_network_type                                                           as before_network_type    --到件站点上一站站点类型
     , t2.same_city_port_type                                                                        as same_city_port_type    --同城归属1进港2出港
     , t2.transfer_port_type                                                                         as transfer_port_type     --中转归属1进港2出港
     , t3.send_station_send_later_time                                                               as send_pack_later_time   --到件站点包对应发件时间
--      , if((unix_timestamp(t3.send_station_send_later_time) -
--            unix_timestamp(t1.send_station_arrival_early_time)) / 3600 > 24, 1,
--           0)                                  as send_pack_falg         --24小时内没有进行包发件
     , if(t1.sent_station_arrival_packagecode is not null and t1.sent_station_arrival_packagecode <> '' and
          (t1.sent_station_send_packagecode <> t1.sent_station_arrival_packagecode or
           t1.sent_station_send_packagecode is null or t1.sent_station_send_packagecode = ''), 1, 0) as send_pack_falg
     , date_add('{{ execution_date | cst_ds }}', -1)                                                                    as date_time              --日期
     , t4.agent_code
     , t4.agent_name
     , t1.send_station_send_next_network_code                                                        as plan_next_network_code --发件规划下一站
     , t1.send_station_send_next_network_type                                                        as plan_next_network_type --发件规划下一站类型
     , t1.send_station_unbagging_packagecode                                                         as unbagging_packagecode  --卸车包号
     , date_add('{{ execution_date | cst_ds }}', -1)                                                                    as dt
from result_table t1
         left join
     (
         select site_code
              , same_city_port_type --同城归属 1：进港 2：出港
              , transfer_port_type  --中转归属 1：进港 2：出港
         from jms_dim.dim_site_operate_data_config_base
     ) t2
     on t1.send_station_network_code = t2.site_code
         left join
     (
         select send_station_network_code
              , sent_station_send_packagecode
              , min(send_station_send_later_time) as send_station_send_later_time
         from result_table
         group by send_station_network_code
                , sent_station_send_packagecode
     ) t3
     on t1.send_station_network_code = t3.send_station_network_code
         and t1.sent_station_arrival_packagecode = t3.sent_station_send_packagecode
         left join jms_dim.dim_network_whole_massage t4
                   on t1.send_station_network_code = t4.code
where t1.type in (1, 2, 3, 4)
distribute by abs(hash(waybill_no)) % 100
;